﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using IndoReputation.Models.BusinessObject;
using System.Data.SqlClient;
using System.Data;
using IndoReputation.Models.BusinessObject.Collections;

namespace IndoReputation.Models.DataAccess
{
    public class MediaDAL
    {
        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:39 AM
        /// Todo: Insert a Object User to Database
        /// </summary>
        /// <param name="media">A user.</param>
        /// <returns></returns>
        public static int Insert(Media media)
        {
            try
            {
                string spName = "Media_Insert";
                SqlParameter[] spPara = new SqlParameter[31];
                spPara[0] = new SqlParameter("@ID", SqlDbType.Int, 2, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Default, null);
                spPara[1] = new SqlParameter("@MediaCategoryID", media.MediaCategoryID);
                spPara[2] = new SqlParameter("@Name", media.Name);
                spPara[3] = new SqlParameter("@ChiefEditorName", media.ChiefEditorName);
                spPara[4] = new SqlParameter("@ChiefEditorMobile", media.ChiefEditorMobile);
                spPara[5] = new SqlParameter("@ChiefEditorEmail", media.ChiefEditorEmail);
                spPara[6] = new SqlParameter("@AddStreetName", media.AddStreetName);
                spPara[7] = new SqlParameter("@AddStreetNumber", media.AddStreetNumber);
                spPara[8] = new SqlParameter("@AddBuildingName", media.AddBuildingName);
                spPara[9] = new SqlParameter("@AddCity", media.AddCity);
                spPara[10] = new SqlParameter("@AddCountry", media.AddCountry);
                spPara[11] = new SqlParameter("@AddPhone", media.AddPhone);
                spPara[12] = new SqlParameter("@AddFax", media.AddStreetName);
                spPara[13] = new SqlParameter("@AddEmail", media.AddEmail);
                spPara[14] = new SqlParameter("@AddWebsite", media.AddWebsite);
                if(media.FirstTimeLaunched == null)
                    spPara[15] = new SqlParameter("@FirstTimeLaunched", DBNull.Value);
                else
                    spPara[15] = new SqlParameter("@FirstTimeLaunched", media.FirstTimeLaunched);
                if (media.TotalPublishedDaily == null)
                    spPara[16] = new SqlParameter("@TotalPublishedDaily", DBNull.Value);
                else
                    spPara[16] = new SqlParameter("@TotalPublishedDaily", media.TotalPublishedDaily);
                if (media.TotalViewersDaily == null)
                    spPara[17] = new SqlParameter("@TotalViewersDaily", DBNull.Value);
                else
                    spPara[17] = new SqlParameter("@TotalViewersDaily", media.TotalViewersDaily);
                spPara[18] = new SqlParameter("@ViewerTypeID", media.ViewerTypeID);
                if (media.TotalUsers == null)
                    spPara[19] = new SqlParameter("@TotalUsers", DBNull.Value);
                else
                    spPara[19] = new SqlParameter("@TotalUsers", media.TotalUsers);
                spPara[20] = new SqlParameter("@OwnershipTypeID", media.OwnershipTypeID);
                spPara[21] = new SqlParameter("@OrientationTypeID", media.OrientationTypeID);
                spPara[22] = new SqlParameter("@IsFreedom", media.IsFreedom);
                spPara[23] = new SqlParameter("@StrongInfluenceTypeID", media.StrongInfluenceTypeID);
                spPara[24] = new SqlParameter("@RankTypeID", media.RankTypeID);
                spPara[25] = new SqlParameter("@Description", media.Description);
                spPara[26] = new SqlParameter("@CreateAt", media.CreateAt);
                spPara[27] = new SqlParameter("@CreateBy", media.CreateBy);
                spPara[28] = new SqlParameter("@UpdateAt", DBNull.Value);
                spPara[29] = new SqlParameter("@UpdateBy", DBNull.Value);
                spPara[30] = new SqlParameter("@IsEnable", media.IsEnable);
                DataAccessBase.ExecuteNonQuery(CommandType.StoredProcedure, spName, spPara);
                return Convert.ToInt32(spPara[0].Value);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:45 AM
        /// Todo: Update a User Exists in Database 
        /// </summary>
        /// <param name="media">A user.</param>
        /// <returns></returns>
        public static bool Update(Media media)
        {
            try
            {
                string spName = "Media_UpdateByPK";
                SqlParameter[] spPara = new SqlParameter[31];
                spPara[0] = new SqlParameter("@ID", media.ID);
                spPara[1] = new SqlParameter("@MediaCategoryID", media.MediaCategoryID);
                spPara[2] = new SqlParameter("@Name", media.Name);
                spPara[3] = new SqlParameter("@ChiefEditorName", media.ChiefEditorName);
                spPara[4] = new SqlParameter("@ChiefEditorMobile", media.ChiefEditorMobile);
                spPara[5] = new SqlParameter("@ChiefEditorEmail", media.ChiefEditorEmail);
                spPara[6] = new SqlParameter("@AddStreetName", media.AddStreetName);
                spPara[7] = new SqlParameter("@AddStreetNumber", media.AddStreetNumber);
                spPara[8] = new SqlParameter("@AddBuildingName", media.AddBuildingName);
                spPara[9] = new SqlParameter("@AddCity", media.AddCity);
                spPara[10] = new SqlParameter("@AddCountry", media.AddCountry);
                spPara[11] = new SqlParameter("@AddPhone", media.AddPhone);
                spPara[12] = new SqlParameter("@AddFax", media.AddStreetName);
                spPara[13] = new SqlParameter("@AddEmail", media.AddEmail);
                spPara[14] = new SqlParameter("@AddWebsite", media.AddWebsite);
                if (media.FirstTimeLaunched == null)
                    spPara[15] = new SqlParameter("@FirstTimeLaunched", DBNull.Value);
                else
                    spPara[15] = new SqlParameter("@FirstTimeLaunched", media.FirstTimeLaunched);
                if (media.TotalPublishedDaily == null)
                    spPara[16] = new SqlParameter("@TotalPublishedDaily", DBNull.Value);
                else
                    spPara[16] = new SqlParameter("@TotalPublishedDaily", media.TotalPublishedDaily);
                if (media.TotalViewersDaily == null)
                    spPara[17] = new SqlParameter("@TotalViewersDaily", DBNull.Value);
                else
                    spPara[17] = new SqlParameter("@TotalViewersDaily", media.TotalViewersDaily);
                spPara[18] = new SqlParameter("@ViewerTypeID", media.ViewerTypeID);
                if (media.TotalUsers == null)
                    spPara[19] = new SqlParameter("@TotalUsers", DBNull.Value);
                else
                    spPara[19] = new SqlParameter("@TotalUsers", media.TotalUsers);
                spPara[20] = new SqlParameter("@OwnershipTypeID", media.OwnershipTypeID);
                spPara[21] = new SqlParameter("@OrientationTypeID", media.OrientationTypeID);
                spPara[22] = new SqlParameter("@IsFreedom", media.IsFreedom);
                spPara[23] = new SqlParameter("@StrongInfluenceTypeID", media.StrongInfluenceTypeID);
                spPara[24] = new SqlParameter("@RankTypeID", media.RankTypeID);
                spPara[25] = new SqlParameter("@Description", media.Description);
                spPara[26] = new SqlParameter("@CreateAt", media.CreateAt);
                spPara[27] = new SqlParameter("@CreateBy", media.CreateBy);
                spPara[28] = new SqlParameter("@UpdateAt", DBNull.Value);
                spPara[29] = new SqlParameter("@UpdateBy", DBNull.Value);
                spPara[30] = new SqlParameter("@IsEnable", media.IsEnable);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:36 AM
        /// Todo: Delete a User Existst in Database
        /// </summary>
        /// <param name="user">A user.</param>
        /// <returns></returns>
        public static bool Delete(int mediaID)
        {
            try
            {
                string spName = "Media_DeleteByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", mediaID);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static MediaList GetAllMedia()
        {
            try
            {
                MediaList listMedia = null;
                string spName = "Media_SelectAll";
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listMedia = new MediaList();
                                List<Media> listTemp = DataAccessBase.ConvertToList<Media>(ds.Tables[0]);
                                foreach (Media _r in listTemp)
                                    listMedia.Add(_r);
                            }
                        }
                    }
                }
                return listMedia;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:40 AM
        /// Todo: Select a User existst in Database
        /// </summary>
        /// <param name="mediaID">The user ID.</param>
        /// <returns></returns>
        public static Media GetMediaById(int mediaID)
        {
            try
            {
                Media media = null;
                string spName = "Media_SelectByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", mediaID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                media = DataAccessBase.ConvertToEntity<Media>(ds.Tables[0].Rows[0]);
                            }
                        }
                    }
                }
                return media;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static MediaJoinList GetAllMediaJoin()
        {
            try
            {
                MediaJoinList listMedia = null;
                string spName = "Media_SelectAllJoin";
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listMedia = new MediaJoinList();
                                List<MediaJoin> listTemp = DataAccessBase.ConvertToList<MediaJoin>(ds.Tables[0]);
                                foreach (MediaJoin _r in listTemp)
                                    listMedia.Add(_r);
                            }
                        }
                    }
                }
                return listMedia;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static MediaList GetAllMediaTopN(int top = 10, int countryID = 0)
        {
            try
            {
                MediaList listMedia = null;
                string spName = "Media_SelectTopnAllJoin";
                SqlParameter[] spPara = new SqlParameter[2];
                spPara[0] = new SqlParameter("@top", top);
                spPara[1] = new SqlParameter("@@countryID", countryID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listMedia = new MediaList();
                                List<Media> listTemp = DataAccessBase.ConvertToList<Media>(ds.Tables[0]);
                                foreach (Media _r in listTemp)
                                    listMedia.Add(_r);
                            }
                        }
                    }
                }
                return listMedia;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static Int64 GetCountChart(DateTime dtFrom, DateTime dtTo, int regionID = 0, int countryID = 0)
        {
            try
            {
                Int64 result = 0;
                string spName = "Media_SelectCountChart";
                SqlParameter[] spPara = new SqlParameter[4];
                spPara[0] = new SqlParameter("@dateFrom", dtFrom);
                spPara[1] = new SqlParameter("@dateTo", dtTo);
                spPara[2] = new SqlParameter("@regionID", regionID);
                spPara[3] = new SqlParameter("@countryID", countryID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                result = Convert.ToInt64(ds.Tables[0].Rows[0]["Total"]);
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
    }
}